package com.neuedu.oasystem.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.neuedu.oasystem.dao.IPlanDao;
import com.neuedu.oasystem.entity.Plan;
import com.neuedu.oasystem.util.DBHelper;

/**
 * 工作计划表接口实现类
 * @author admin hd
 *
 */

public class PlanDaoImpl extends BaseDao implements IPlanDao{
	@Override
	public boolean add(Plan param) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		try {
		String sql = "insert into plan(plan_id,plan_theme,meeting_theme,develop_content,notice_content,plan_type,p_start,p_finish,plan_leader,emp_id) values(?,?,?,?,?,?,?,?,?,?)";
		
		stmt = conn.prepareStatement(sql);
		stmt.setString(1, param.getPlanId());
		stmt.setString(2, param.getPlanTheme());
		stmt.setString(3, param.getMeetingTheme());
		stmt.setString(4, param.getDevelopContent());
		stmt.setString(5, param.getNoticeContent());
		stmt.setString(6, param.getPlanType());
		stmt.setTimestamp(7, new java.sql.Timestamp(param.getPstart().getTime()));
		stmt.setTimestamp(8, new java.sql.Timestamp(param.getPfinish().getTime()));
		stmt.setString(9, param.getPlanLeader());
		stmt.setInt(10, param.getEmpId());
		int count = stmt.executeUpdate();
		if(count==1) {
			return true;
		}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(null, stmt, conn);
		}
		return false;
	}

	@Override
	public boolean edit(Plan param) {
		Connection conn =DBHelper.openConnection();
		PreparedStatement stmt = null;
		try {
		String sql = " update plan set plan_theme=?,meeting_theme=?,develop_content=?,notice_content=?,plan_type=?,p_start=?,p_finish=?,plan_leader=?,emp_id=? where plan_id=?";
		
		stmt = conn.prepareStatement(sql);
		stmt = conn.prepareStatement(sql);
		stmt.setString(10, param.getPlanId());
		stmt.setString(1, param.getPlanTheme());
		stmt.setString(2, param.getMeetingTheme());
		stmt.setString(3, param.getDevelopContent());
		stmt.setString(4, param.getNoticeContent());
		stmt.setString(5, param.getPlanType());
		stmt.setTimestamp(6, new java.sql.Timestamp(param.getPstart().getTime()));
		stmt.setTimestamp(7, new java.sql.Timestamp(param.getPfinish().getTime()));
		stmt.setString(8, param.getPlanLeader());
		stmt.setInt(9, param.getEmpId());
		int count = stmt.executeUpdate();
		if(count==1) {
			return true;
		}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(null, stmt, conn);
		}
		return false;

	}

	@Override
	public boolean deleteById(int id) {
		Connection conn =DBHelper.openConnection();
		PreparedStatement stmt = null;
		try {
		String sql = " delete from plan where planId=?";
		
		stmt = conn.prepareStatement(sql);
		stmt.setInt(1, id);
		int count = stmt.executeUpdate();
		if(count==1) {
			return true;
		}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(null, stmt, conn);
		}
		return false;
	}

	@Override
	public Plan findById(int id) {
		Plan plans = null;
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "select plan_id,plan_theme,meeting_theme,develop_content,notice_content,plan_type,p_start,p_finish,plan_leader,emp_id from plan where plan_id=?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			if (rs.next()) {
				String planId = rs.getString(1);
				String planTheme = rs.getString("planTheme");
				String meetingTheme = rs.getString("meetingTheme");
				String developContent = rs.getString("developContent");
				String noticeContent = rs.getString("noticeContent");
				String planType = rs.getString("planType");
				Date pstart = rs.getTimestamp("pstart");
				Date pfinish = rs.getTimestamp("pfinish");
				String planLeader = rs.getString("planLeader");
				int empId = rs.getInt("empId");
				plans = new Plan(planId,planTheme,meetingTheme,developContent,noticeContent,planType,pstart,pfinish,planLeader,empId);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, stmt, conn);
		}
		return plans;
	}

	@Override
	public List<Plan> findAll() {
		List<Plan> list = new ArrayList<>();
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "select plan_id,plan_theme,meeting_theme,develop_content,notice_content,plan_type,p_start,p_finish,plan_leader,emp_id from plan";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while(rs.next()) {
				String planId = rs.getString("plan_id");
				String planTheme = rs.getString("plan_theme");
				String meetingTheme = rs.getString("meeting_theme");
				String developContent = rs.getString("develop_content");
				String noticeContent = rs.getString("notice_content");
				String planType = rs.getString("plan_type");
				Date pstart = rs.getTimestamp("p_start");
				Date pfinish = rs.getTimestamp("p_finish");
				String planLeader = rs.getString("plan_leader");
				int empId = rs.getInt("emp_id");
				Plan plans = new Plan(planId,planTheme,meetingTheme,developContent,noticeContent,planType,pstart,pfinish,planLeader,empId);
				list.add(plans);
		}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(rs, stmt, conn);
		}
		return list;
	}

	@Override
	public List<Plan> findByExample(Plan param) {
		List<Plan> list = new ArrayList<>();
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "select plan_id,plan_theme,meeting_theme,develop_content,notice_content,plan_type,p_start,p_finish,plan_leader from plan where plan_theme like?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(2,"%"+param.getPlanTheme()+"&");
			rs = stmt.executeQuery();
			while(rs.next()) {
				String planId = rs.getString(1);
				String planTheme = rs.getString("planTheme");
				String meetingTheme = rs.getString("meetingTheme");
				String developContent = rs.getString("developContent");
				String noticeContent = rs.getString("noticeContent");
				String planType = rs.getString("planType");
				Date pstart = rs.getTimestamp("pstart");
				Date pfinish = rs.getTimestamp("pfinish");
				String planLeader = rs.getString("planLeader");
				int empId = rs.getInt("empId");
				Plan plans = new Plan(planId,planTheme,meetingTheme,developContent,noticeContent,planType,pstart,pfinish,planLeader,empId);
				list.add(plans);
		}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(rs, stmt, conn);
		}
		return list;
	}
	

	@Override
	public List<Plan> findByPager(int page, int size) {
		List<Plan> list = new ArrayList<>();
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "select * from "
					+"(select rownum rn,plan_id,plan_theme,meeting_theme,develop_content,notice_content,plan_type,p_start,p_finish,plan_leader,emp_id from plan where rownum<=? ) where rn >?";
			stmt = conn.prepareStatement(sql);
			
			stmt.setInt(1, page*size);
			stmt.setInt(2, (page-1)*size);
			
			rs = stmt.executeQuery();
			while(rs.next()) {
				String planId = rs.getString(2);
				String planTheme = rs.getString("planTheme");
				String meetingTheme = rs.getString("meetingTheme");
				String developContent = rs.getString("developContent");
				String noticeContent = rs.getString("noticeContent");
				String planType = rs.getString("planType");
				Date pstart = rs.getTimestamp("pstart");
				Date pfinish = rs.getTimestamp("pfinish");
				String planLeader = rs.getString("planLeader");
				int empId = rs.getInt("empId");
				Plan plans = new Plan(planId,planTheme,meetingTheme,developContent,noticeContent,planType,pstart,pfinish,planLeader,empId);
				list.add(plans);
		}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(rs, stmt, conn);
		}
		return list;
	}	

	@Override
	public int deleteByIds(int[] ids) {
		int result = 0;
		Connection connection = DBHelper.openConnection();
		PreparedStatement stmt = null;
		try {
		connection.setAutoCommit(false);
		String sql = "delete from plan where plan_id =?";
		stmt = connection.prepareStatement(sql);
		for(int id : ids) {
			stmt.setInt(1, id);
			stmt.addBatch();
		}
			int[] counts = stmt.executeBatch();
			for(int i : counts){
				if(i==1) {
					result++;
				}
			}
		
			if(result==ids.length) {
				connection.commit();
				connection.setAutoCommit(true);
			}else {
				connection.rollback();
			}
		}catch(SQLException e) {
			e.printStackTrace();
			if(connection!=null) {
			try{
				connection.rollback();
				connection.setAutoCommit(true);
			}catch(SQLException e1) {
				
			}
			}
		}finally {
			DBHelper.closeAll(null, stmt, connection);
		}
		return result;
	}

	@Override
	public int addAll(List<Plan> useparamrs) {
		int result = 0;
		Connection connection = DBHelper.openConnection();
		PreparedStatement stmt = null;

		try {
			connection.setAutoCommit(false);
			String sql = "insert into plan(plan_id,plan_theme,meeting_theme,develop_content,notice_content,plan_type,p_start,p_finish,plan_leader,emp_id) values(SEQ_USERS.NEXTVAL,?,?,?,?,?,?,?,?)";
			stmt = connection.prepareStatement(sql);
			for (Plan plan: useparamrs) {
				stmt.setString(1, plan.getPlanId());
				stmt.setString(2, plan.getPlanTheme());
				stmt.setString(3, plan.getMeetingTheme());
				stmt.setString(4, plan.getDevelopContent());
				stmt.setString(5, plan.getNoticeContent());
				stmt.setString(6, plan.getPlanType());
				stmt.setTimestamp(7, new java.sql.Timestamp(plan.getPstart().getTime()));
				stmt.setTimestamp(8, new java.sql.Timestamp(plan.getPfinish().getTime()));
				stmt.setString(9, plan.getPlanLeader());
				stmt.setInt(10, plan.getEmpId());
				stmt.addBatch();
			}
			int[] counts = stmt.executeBatch();
			for (int i : counts) {
				if (i == 1) {
					result++;
				}
			}
			if (result == useparamrs.size()) {
				connection.commit();
				connection.setAutoCommit(true);
			} else {
				connection.rollback();
			}

		} catch (SQLException e) {
			e.printStackTrace();
			if (connection != null) {
				try {
					connection.rollback();
					connection.setAutoCommit(true);
				} catch (SQLException e1) {
				}
			}

		} finally {
			DBHelper.closeAll(null, stmt, connection);
		}
		return result;
	}

	@Override
	public int editAll(List<Plan> param) {
		int result = 0;
		Connection connection = DBHelper.openConnection();
		PreparedStatement stmt = null;
		try {
			connection.setAutoCommit(false);
			String sql = " update plan set plan_id=?,plan_theme=?,meeting_theme=?,develop_content=?,notice_content=?,plan_type=?,p_start=?,p_finish=?,plan_leader=?,emp_id";
			stmt = connection.prepareStatement(sql);
			for (Plan plan: param) {
				stmt.setString(1, plan.getPlanId());
				stmt.setString(2, plan.getPlanTheme());
				stmt.setString(3, plan.getMeetingTheme());
				stmt.setString(4, plan.getDevelopContent());
				stmt.setString(5, plan.getNoticeContent());
				stmt.setString(6, plan.getPlanType());
				stmt.setTimestamp(7, new java.sql.Timestamp(plan.getPstart().getTime()));
				stmt.setTimestamp(8, new java.sql.Timestamp(plan.getPfinish().getTime()));
				stmt.setString(9, plan.getPlanLeader());
				stmt.setInt(10, plan.getEmpId());
				stmt.addBatch();
			}
			int[] counts = stmt.executeBatch();
			for (int i : counts) {
				if (i == 1) {
					result++;
				}
			}
			if (result == param.size()) {
				connection.commit();
				connection.setAutoCommit(true);
			} else {
				connection.rollback();
			}

		} catch (SQLException e) {
			e.printStackTrace();
			if (connection != null) {
				try {
					connection.rollback();
					connection.setAutoCommit(true);
				} catch (SQLException e1) {
				}
			}

		} finally {
			DBHelper.closeAll(null, stmt, connection);
		}
		return result;
	}
	
}
